<?php
/**
 * Created by PhpStorm.
 * User: zhao
 * Date: 2018/4/4
 * Time: 9:26
 */
class ToolController extends Controller
{
    protected $_tree_data;

    public $layout = '//layouts/basic_tool';

    static $_list=[];

    public function actionAgentTree()
    {
        //获取所有的代理商
        $agents = app()->db->createCommand(
            'SELECT a.id_agent,a.`name`,a.phone,a.parent_id,a.id_number,a.rgt,a.lft,b.bank_name,b.card_no
             FROM agent a LEFT JOIN bank_card b
             ON a.id_agent = b.id_agent'
        )->queryAll();

        foreach ($agents as &$agent){

            //获取每个代理商的子集
            $agent['children'] = app()->db->createCommand(
                "SELECT group_concat(id_agent) as ids
                 FROM agent
                 WHERE lft < :rgt AND rgt > :lft"
            )->bindParam(':rgt', $agent['rgt'], PDO::PARAM_STR)
            ->bindParam(':lft', $agent['lft'], PDO::PARAM_STR)
            ->queryAll();
            $agent['agent_ids'] = $agent['children'][0]['ids'];

            //开户数
            $agent['merchent_ids'] = app()->db->createCommand(
                "SELECT group_concat(id_merchant) as m_ids FROM `merchant` WHERE id_agent IN ({$agent['agent_ids']},{$agent['id_agent']})"
            )->queryAll();
            $agent['merchent_ids'] = $agent['merchent_ids'][0]['m_ids'];
            $agent['merchent_count'] = substr_count($agent['merchent_ids'],',')+1;

            //红包数
            $agent['trade_data'] = app()->db->createCommand(
                "SELECT sum(`discount`) as discount_sum, sum(`amount_due`) as amount_due_sum ,COUNT(*) as trade_sum FROM `tradedetail` 
                  WHERE id_merchant IN ({$agent['merchent_ids']})
                  AND trade_status='1'"
            )->queryAll();

        }

        $res = $this->tree($agents);

        $this->render('agenttree',[
            'agents'=>$res
        ]);
    }

    public function actionAgentAjax()
    {
        $start_time = app()->request->getPost('start_time');
        $end_time = app()->request->getPost('end_time');
        $start_time = date('Y-m-d H:i:s',strtotime($start_time));
        $end_time = date('Y-m-d H:i:s',strtotime($end_time));

        //获取所有的代理商
        $agents = app()->db->createCommand(
            'SELECT a.id_agent,a.`name`,a.phone,a.parent_id,a.id_number,a.rgt,a.lft,b.bank_name,b.card_no
             FROM agent a LEFT JOIN bank_card b
             ON a.id_agent = b.id_agent'
        )->queryAll();

        foreach ($agents as &$agent){

            //获取每个代理商的子集
            $agent['children'] = app()->db->createCommand(
                "SELECT group_concat(id_agent) as ids
                 FROM agent
                 WHERE lft < :rgt AND rgt > :lft"
            )->bindParam(':rgt', $agent['rgt'], PDO::PARAM_STR)
                ->bindParam(':lft', $agent['lft'], PDO::PARAM_STR)
                ->queryAll();
            $agent['agent_ids'] = $agent['children'][0]['ids'];

            //开户数
            $agent['merchent_ids'] = app()->db->createCommand(
                "SELECT group_concat(id_merchant) as m_ids FROM `merchant` WHERE id_agent IN ({$agent['agent_ids']},{$agent['id_agent']})"
            )->queryAll();
            $agent['merchent_ids'] = $agent['merchent_ids'][0]['m_ids'];
            $agent['merchent_count'] = substr_count($agent['merchent_ids'],',')+1;

            //红包数
            $agent['trade_data'] = app()->db->createCommand(
                "SELECT sum(`discount`) as discount_sum, sum(`amount_due`) as amount_due_sum ,COUNT(*) as trade_sum FROM `tradedetail` 
                  WHERE id_merchant IN ({$agent['merchent_ids']})
                  AND trade_status='1'
                  AND paytime>:start_time
                  AND paytime<:end_time"
            )->bindParam(':start_time',$start_time,PDO::PARAM_STR)
                ->bindParam(':end_time',$end_time,PDO::PARAM_STR)
                ->queryAll();

        }

        $res = $this->tree($agents);

        return $this->renderPartial('agent_list',[
            'agents'=>$res
        ]);
    }

    public function actionDetailAjax()
    {
        $start_time = app()->request->getPost('start_time');
        $end_time = app()->request->getPost('end_time');
        $start_time = date('Y-m-d H:i:s',strtotime($start_time));
        $end_time = date('Y-m-d H:i:s',strtotime($end_time));

        $agent_id = app()->request->getParam('agent_id');

        $agent = app()->db->createCommand('SELECT id_agent,rgt,lft,`name` FROM `agent` WHERE id_agent = :id_agent ')
            ->bindParam('id_agent',$agent_id,PDO::PARAM_STR)
            ->queryAll();

        if(empty($agent)) return 'null';
        $agent = $agent[0];

        //获取每个代理商的子集
        $agent['children'] = app()->db->createCommand(
            "SELECT group_concat(id_agent) as ids
                 FROM agent 
                 WHERE lft < :rgt AND rgt > :lft"
        )->bindParam(':rgt', $agent['rgt'], PDO::PARAM_STR)
            ->bindParam(':lft', $agent['lft'], PDO::PARAM_STR)
            ->queryAll();
        $agent['agent_ids'] = $agent['children'][0]['ids'];

        //查找代理商下的所有门店
        //开户数
        $agent['merchents'] = app()->db->createCommand(
            "SELECT * FROM `merchant` WHERE id_agent IN ({$agent['agent_ids']},{$agent['id_agent']})"
        )->queryAll();
        $ids = '';
        foreach ($agent['merchents'] as &$merchant){
            $ids .= $merchant['id_merchant'].',';
        }
        $ids = trim($ids,',');

        //门店明细
        foreach ($agent['merchents'] as &$merchant){
            $merchant['trade_data'] = app()->db->createCommand(
                "SELECT sum(`discount`) as discount_sum, sum(`amount_due`) as amount_due_sum ,COUNT(*) as trade_sum FROM `tradedetail` 
                  WHERE id_merchant={$merchant['id_merchant']}
                  AND trade_status='1'
                  AND paytime>:start_time
                  AND paytime<:end_time"
            )->bindParam(':start_time',$start_time,PDO::PARAM_STR)
                ->bindParam(':end_time',$end_time,PDO::PARAM_STR)
                ->queryAll();
        }

        $this->renderPartial('datail_list',[
            'detail'=>$agent
        ]);
    }


    public function actionAgnetDetail()
    {
        $agent_id = app()->request->getParam('agent_id');

        $agent = app()->db->createCommand('SELECT id_agent,rgt,lft,`name` FROM `agent` WHERE id_agent = :id_agent ')
            ->bindParam('id_agent',$agent_id,PDO::PARAM_STR)
            ->queryAll();

        if(empty($agent)) return 'null';
        $agent = $agent[0];

        //获取每个代理商的子集
        $agent['children'] = app()->db->createCommand(
            "SELECT group_concat(id_agent) as ids
                 FROM agent 
                 WHERE lft < :rgt AND rgt > :lft"
        )->bindParam(':rgt', $agent['rgt'], PDO::PARAM_STR)
            ->bindParam(':lft', $agent['lft'], PDO::PARAM_STR)
            ->queryAll();
        $agent['agent_ids'] = $agent['children'][0]['ids'];

        //查找代理商下的所有门店
        //开户数
        $agent['merchents'] = app()->db->createCommand(
            "SELECT * FROM `merchant` WHERE id_agent IN ({$agent['agent_ids']},{$agent['id_agent']})"
        )->queryAll();
        $ids = '';
        foreach ($agent['merchents'] as &$merchant){
            $ids .= $merchant['id_merchant'].',';
        }
        $ids = trim($ids,',');

        //门店明细
        foreach ($agent['merchents'] as &$merchant){
            $merchant['trade_data'] = app()->db->createCommand(
                "SELECT sum(`discount`) as discount_sum, sum(`amount_due`) as amount_due_sum ,COUNT(*) as trade_sum FROM `tradedetail` 
                  WHERE id_merchant={$merchant['id_merchant']}
                  AND trade_status='1'"
            )->queryAll();
        }

        $this->render('agentdatail',[
            'detail'=>$agent
        ]);
    }

    public function tree($arr,$pid='',$level=0){

        foreach($arr as $value){

            if($value['parent_id']==$pid){

                $value['level']=$level;

                self::$_list = array_merge(self::$_list,[$value]);
                $list[]=$value;
                //递归点
                $this->tree($arr,$value['id_agent'],$level+1);
            }
        }
        return self::$_list;//递归出口
    }

}